<?php 
namespace app\admin\controller;

use app\common\controller\Log;

class Sql extends  Base
{
	/**
	 * 数据库列表
	 * @return unknown
	 */
	public function index()
	{
		//获取数据库列表
		$tables = Db()->query($sql = 'SHOW TABLE STATUS'); //获取所有表信息
		foreach ($tables as $k=>&$rows){
			if($rows['Name'] == 'db_admin' || $rows['Name'] == 'db_menu' || $rows['Name'] == 'db_sys_param'){
				unset($tables[$k]);
			}
			$rows['checked'] = 0;
			$rows['count'] = count(Db()->query($sql = 'SHOW COLUMNS FROM '.$rows['Name'].''));
		}
		sort($tables);
		return jsonSuccess($tables);
	}

	/**
	 * 数据库备份
	 * @return unknown
	 */
	public function backUp()
	{
		$database = config("database");
		$host=$database['hostname'];
		$user=$database['username'];//数据库账号
		$password=$database['password'];//数据库密码
		$dbname=$database['database'];//数据库名称
		$link = mysqli_connect($host,$user,$password);
		if(!mysqli_connect($host,$user,$password)) //连接mysqli数据库
		{
			return jsonError('数据库连接失败，请核对后再试');
		}
		if(!mysqli_select_db($link,$dbname)) //是否存在该数据库
		{
			return jsonError('不存在数据库:'.$dbname.',请核对后再试');
		}
		mysqli_query($link,"set names 'utf8'");
		$mysqli= "set charset utf8;\r\n";
		$q1 = mysqli_query($link, "show tables");
		while($t=mysqli_fetch_array($q1)){
			$table=$t[0];
			$q2=mysqli_query($link,"show create table `$table`");
			$sql=mysqli_fetch_array($q2);
			$mysqli.=$sql['Create Table'].";\r\n";
			$q3=mysqli_query($link,"select * from `$table`");
			while($data=mysqli_fetch_assoc($q3)){
				$keys=array_keys($data);
				$keys=array_map('addslashes',$keys);
				$keys=join('`,`',$keys);
				$keys="`".$keys."`";
				$vals=array_values($data);
				$vals = json_encode($vals,JSON_UNESCAPED_UNICODE);
				$vals = substr($vals,0,strlen($vals)-1);
				$vals = substr($vals,1);
				$mysqli.="insert into `$table`($keys) values($vals);\r\n";
			}
		}
		//数据库备份路径
		$saveFileName = $database['sql_back_row'];
		$filename=$saveFileName.'/'.date('YmdHis').'.sql'; //存放路径，默认存放到项目最外层
		$fp = fopen($filename,'w');
		fputs($fp,$mysqli);
		fclose($fp);
		return jsonSuccess('备份成功');
	}

	public function sqlList()
	{
		$database = config("database");
		$saveFileName = $database['sql_back_row'];
		$path = [];
		if(is_dir($saveFileName)){
			$dh =(opendir($saveFileName));
			if($dh){
				while (($file = readdir($dh))!= false){
					//文件名的全路径 包含文件名
					$filePath = $file;
					if(strlen($filePath) > 3 && $filePath!= '.gitignore'){
						array_push($path,[
								'path'=>$filePath,
								'dir'=>$saveFileName.$file,
								'size' => round((filesize($saveFileName.$file)/ 1024),2).'kb'
						]);
					}
				}
				closedir($dh);
				$path = array_values($path);
				return jsonSuccess($path);
			}
		}else{
			return jsonError([]);
		}
	}
}